#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @File    ：config_db
# @Author  ：曹庆
# @Date    ：2024/8/6 21:35
import pymysql
import threading
from datetime import datetime

# host = '127.0.0.1'
# user = 'root'
# password = 'root'
# db = 'root'


class SingletonMeta(type):
    """
    This is a thread-safe implementation of Singleton.
    """
    _instances = {}
    _lock: threading.Lock = threading.Lock()

    def __call__(cls, *args, **kwargs):
        with cls._lock:
            if cls not in cls._instances:
                instance = super().__call__(*args, **kwargs)
                cls._instances[cls] = instance
        return cls._instances[cls]


class CofDatabaseConnection:#(metaclass=SingletonMeta):
    def __init__(self, host, user, password, database):
        self.connection = pymysql.connect(
            host=host,
            user=user,
            password=password,
            database=database,
            cursorclass=pymysql.cursors.DictCursor
        )
        self.table = 'ip_host'

    def get_connection(self):
        return self.connection

    def get_addr_user_pwd_by_ip(self, ip_addr):
        query = f'SELECT * FROM {self.table} WHERE ip_addr=%s;'
        params = (ip_addr,)
        with self.connection.cursor() as cursor:
            cursor.execute(query, params)
            result = cursor.fetchone()
        return result

    def get_databases(self):
        query = 'SHOW DATABASES;'
        with self.connection.cursor() as cursor:
            cursor.execute(query=query)
            result = cursor.fetchall()
        dbs = []
        for item in result:
            dbs.append(item['Database'])
        return dbs

    def get_tables(self, db):
        query = f'SHOW TABLES FROM {db};'
        print(query)
        with self.connection.cursor() as cursor:
            cursor.execute(query=query)
            result = cursor.fetchall()
        tables = []
        for item in result:
            tmp = list(item.values())
            tables.extend(tmp)
        return tables

    def get_comment_tabe_key(self, db, table):
        query = f"SELECT COLUMN_NAME AS col, COLUMN_COMMENT AS comment FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='{db}' AND TABLE_NAME='{table}'"
        with self.connection.cursor() as cursor:
            cursor.execute(query=query)
            result = cursor.fetchall()
        comment = {}
        for item in result:
            if item['col'] == 'znz_create_time' or item['col'] == 'create_time':
                item['comment'] = '创建时间'
            if item['col'] == 'znz_update_time' or item['col'] == 'update_time':
                item['comment'] = '更新时间'
            comment[item['col']] = item['comment'] + f'({item['col']})'
        return comment

    def get_data(self, db, table, limit=10, page=1):
        limit = int(limit)
        page = int(page)
        offset = (page - 1) * limit
        query = f'SELECT * FROM {db}.{table} limit {limit} offset {offset}'
        print(query)
        with self.connection.cursor() as cursor:
            cursor.execute(query=query)
            result = cursor.fetchall()
        comment = self.get_comment_tabe_key(db, table)
        data = []
        keys = tuple(comment.keys())
        data.append([comment[key] for key in keys])
        for item in result:
            data.append([datetime.strftime(item[key], '%Y-%m-%d %H:%M:%S') if isinstance(item[key], datetime) else item[key] for key in keys])
        count = self.count(db, table)
        return (data, count)
    
    def count(self, db, table):
        query = f'SELECT COUNT(*) AS COUNT FROM {db}.{table}'
        with self.connection.cursor() as cursor:
            cursor.execute(query=query)
            result = cursor.fetchone()
        return result['COUNT']